package com.milling.common.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
//drillDiameter
public class ExcelDrillParser {

    // 定义数据模型类，映射Excel行数据
    public static class Diameter {
        private String toolCode;      // 刀具
        private String finishedAperture; // 成品孔径
        private String pth;           // PTH
        private String drillDiameter; // 钻咀直径
        private String pcsHoleCount;  // PCS孔数
        private String paHoleCount;  // PA孔数
        private String type;
        private String remark;
        // 其他字段根据需求继续添加...

        public String getToolCode() {
            return toolCode;
        }

        public void setToolCode(String toolCode) {
            this.toolCode = toolCode;
        }

        public String getFinishedAperture() {
            return finishedAperture;
        }

        public void setFinishedAperture(String finishedAperture) {
            this.finishedAperture = finishedAperture;
        }

        public String getPth() {
            return pth;
        }

        public void setPth(String pth) {
            this.pth = pth;
        }

        public String getDrillDiameter() {
            return drillDiameter;
        }

        public void setDrillDiameter(String drillDiameter) {
            this.drillDiameter = drillDiameter;
        }

        public String getPcsHoleCount() {
            return pcsHoleCount;
        }

        public void setPcsHoleCount(String pcsHoleCount) {
            this.pcsHoleCount = pcsHoleCount;
        }

        public String getPaHoleCount() {
            return paHoleCount;
        }

        public void setPaHoleCount(String paHoleCount) {
            this.paHoleCount = paHoleCount;
        }

        public String getType() {
            return type;
        }

        public void setType(String type) {
            this.type = type;
        }

        public String getRemark() {
            return remark;
        }

        public void setRemark(String remark) {
            this.remark = remark;
        }

        @Override
        public String toString() {
            return "DrillInfo{" +
                    "toolCode='" + toolCode + '\'' +
                    ", finishedAperture='" + finishedAperture + '\'' +
                    ", pth='" + pth + '\'' +
                    ", drillDiameter='" + drillDiameter + '\'' +
                    ", pcsHoleCount='" + pcsHoleCount + '\'' +
                    ", paHoleCount='" + paHoleCount + '\'' +
                    ", type='" + type + '\'' +
                    ", remark='" + remark + '\'' +
                    '}';
        }
// Getters and Setters
        // 可根据需要生成toString()方法
    }

    public static List<Diameter> parseExcel(String filePath) throws Exception {
        List<Diameter> drillList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) { // 处理.xlsx文件

            Sheet sheet = workbook.getSheet("Table1"); // 获取指定工作表
            if (sheet == null) {
                throw new RuntimeException("工作表 'Table1' 不存在");
            }

            // 跳过表头（假设第一行为表头）
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;

                Diameter info = new Diameter();

                // 解析每一列（按Excel列顺序）
                info.setToolCode(getCellStringValue(row.getCell(0)));       // A列：刀具
                info.setFinishedAperture(getCellStringValue(row.getCell(2))); // C列：成品孔径
                info.setPth(getCellStringValue(row.getCell(3)));            // D列：PTH
                info.setDrillDiameter(getCellStringValue(row.getCell(6)));  // G列：钻咀直径
                info.setPcsHoleCount(getCellStringValue(row.getCell(7)));   // H列：PCS孔数
                info.setPaHoleCount(getCellStringValue(row.getCell(13)));    // N列：PA孔数
                info.setType(getCellStringValue(row.getCell(15)));          // P列：类型
                info.setRemark(getCellStringValue(row.getCell(16)));         // Q列：备注

                drillList.add(info);
            }
        }

        return drillList;
    }

    // 安全获取单元格字符串值（处理空单元格和不同类型）
    private static String getCellStringValue(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    public static void main(String[] args) {
        try {
            String filePath = "钻孔表101y106808c00178a0-drl.xlsx";
            List<Diameter> drillData = parseExcel(filePath);

            // 打印前5行验证结果
            System.out.println("成功解析到 " + drillData.size() + " 条数据：");
            for (int i = 0; i < drillData.size(); i++) {
//            for (int i = 0; i < Math.min(5, drillData.size()); i++) {
                Diameter info = drillData.get(i);
                System.out.printf(
                        "刀具: %s, 孔径: %s, 钻咀直径: %s, PA孔数: %s%n, 类型: %s, 备注: %s",
                        info.getToolCode(), info.getFinishedAperture(),
                        info.getDrillDiameter(), info.getPaHoleCount(), info.getType(), info.getRemark()
                );
            }
        } catch (Exception e) {
            System.err.println("解析失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
}
